const { pool } = require('../config/db');
const { success, error } = require('../utils/response');
const moment = require('moment');

// ===== 用户端 =====

// 获取公告列表（用户端）
exports.getPublicAnnouncements = async (req, res, next) => {
  try {
    const { page = 1, limit = 10, type } = req.query;
    const offset = (page - 1) * limit;

    let whereClause = 'WHERE status = "published" AND publish_time <= NOW()';
    let params = [];

    // 过滤未过期的公告
    whereClause += ' AND (expire_time IS NULL OR expire_time > NOW())';

    if (type) {
      whereClause += ' AND type = ?';
      params.push(type);
    }

    const [announcements] = await pool.query(`
      SELECT
        id,
        title,
        content,
        type,
        priority,
        publish_time,
        expire_time,
        view_count
      FROM announcements
      ${whereClause}
      ORDER BY priority DESC, publish_time DESC
      LIMIT ? OFFSET ?
    `, [...params, parseInt(limit), offset]);

    const [total] = await pool.query(`
      SELECT COUNT(*) as count FROM announcements ${whereClause}
    `, params);

    success(res, {
      announcements,
      pagination: {
        current: parseInt(page),
        limit: parseInt(limit),
        total: total[0].count,
        pages: Math.ceil(total[0].count / limit)
      }
    });
  } catch (err) {
    next(err);
  }
};

// 获取公告详情（用户端）
exports.getAnnouncementDetail = async (req, res, next) => {
  try {
    const { id } = req.params;

    const [announcements] = await pool.query(`
      SELECT
        id,
        title,
        content,
        type,
        priority,
        publish_time,
        expire_time,
        view_count
      FROM announcements
      WHERE id = ? AND status = 'published'
    `, [id]);

    if (announcements.length === 0) {
      return error(res, '公告不存在', 404);
    }

    // 增加浏览次数
    await pool.query('UPDATE announcements SET view_count = view_count + 1 WHERE id = ?', [id]);

    success(res, announcements[0]);
  } catch (err) {
    next(err);
  }
};

// 获取首页公告（用户端）
exports.getHomeAnnouncements = async (req, res, next) => {
  try {
    const [announcements] = await pool.query(`
      SELECT
        id,
        title,
        type,
        priority,
        publish_time
      FROM announcements
      WHERE status = 'published' 
        AND publish_time <= NOW()
        AND (expire_time IS NULL OR expire_time > NOW())
      ORDER BY priority DESC, publish_time DESC
      LIMIT 5
    `);

    success(res, announcements);
  } catch (err) {
    next(err);
  }
};

// ===== 管理员端 =====

// 获取所有公告（管理员）
exports.getAllAnnouncements = async (req, res, next) => {
  try {
    const { page = 1, limit = 10, status, type, keyword } = req.query;
    const offset = (page - 1) * limit;

    let whereClause = 'WHERE 1=1';
    let params = [];

    if (status) {
      whereClause += ' AND a.status = ?';
      params.push(status);
    }

    if (type) {
      whereClause += ' AND a.type = ?';
      params.push(type);
    }

    if (keyword) {
      whereClause += ' AND (a.title LIKE ? OR a.content LIKE ?)';
      params.push(`%${keyword}%`, `%${keyword}%`);
    }

    const [announcements] = await pool.query(`
      SELECT
        a.id,
        a.title,
        a.content,
        a.type,
        a.priority,
        a.status,
        a.publish_time,
        a.expire_time,
        a.view_count,
        a.create_time,
        a.update_time,
        au.username as author_name
      FROM announcements a
      LEFT JOIN admin_users au ON a.author_id = au.id
      ${whereClause}
      ORDER BY a.create_time DESC
      LIMIT ? OFFSET ?
    `, [...params, parseInt(limit), offset]);

    const [total] = await pool.query(`
      SELECT COUNT(*) as count FROM announcements a ${whereClause}
    `, params);

    success(res, {
      announcements,
      pagination: {
        current: parseInt(page),
        limit: parseInt(limit),
        total: total[0].count,
        pages: Math.ceil(total[0].count / limit)
      }
    });
  } catch (err) {
    next(err);
  }
};

// 创建公告（管理员）
exports.createAnnouncement = async (req, res, next) => {
  try {
    const { title, content, type, priority, status, publish_time, expire_time } = req.body;
    const author_id = req.userId;

    if (!title || !content) {
      return error(res, '标题和内容不能为空', 400);
    }

    const [result] = await pool.query(`
      INSERT INTO announcements 
      (title, content, type, priority, status, publish_time, expire_time, author_id)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `, [
      title,
      content,
      type || 'notice',
      priority || 0,
      status || 'draft',
      publish_time || null,
      expire_time || null,
      author_id
    ]);

    success(res, {
      id: result.insertId,
      title,
      status: status || 'draft'
    }, '公告创建成功');
  } catch (err) {
    next(err);
  }
};

// 更新公告（管理员）
exports.updateAnnouncement = async (req, res, next) => {
  try {
    const { id } = req.params;
    const { title, content, type, priority, status, publish_time, expire_time } = req.body;

    // 检查公告是否存在
    const [existing] = await pool.query('SELECT id FROM announcements WHERE id = ?', [id]);
    if (existing.length === 0) {
      return error(res, '公告不存在', 404);
    }

    await pool.query(`
      UPDATE announcements 
      SET title = ?, content = ?, type = ?, priority = ?, 
          status = ?, publish_time = ?, expire_time = ?
      WHERE id = ?
    `, [title, content, type, priority, status, publish_time, expire_time, id]);

    success(res, { id }, '公告更新成功');
  } catch (err) {
    next(err);
  }
};

// 删除公告（管理员）
exports.deleteAnnouncement = async (req, res, next) => {
  try {
    const { id } = req.params;

    // 检查公告是否存在
    const [existing] = await pool.query('SELECT id FROM announcements WHERE id = ?', [id]);
    if (existing.length === 0) {
      return error(res, '公告不存在', 404);
    }

    await pool.query('DELETE FROM announcements WHERE id = ?', [id]);

    success(res, { id }, '公告删除成功');
  } catch (err) {
    next(err);
  }
};

// 发布公告（管理员）
exports.publishAnnouncement = async (req, res, next) => {
  try {
    const { id } = req.params;

    // 检查公告是否存在
    const [existing] = await pool.query('SELECT id, status FROM announcements WHERE id = ?', [id]);
    if (existing.length === 0) {
      return error(res, '公告不存在', 404);
    }

    if (existing[0].status === 'published') {
      return error(res, '公告已发布', 400);
    }

    await pool.query(`
      UPDATE announcements 
      SET status = 'published', publish_time = NOW()
      WHERE id = ?
    `, [id]);

    success(res, { id }, '公告发布成功');
  } catch (err) {
    next(err);
  }
};

// 归档公告（管理员）
exports.archiveAnnouncement = async (req, res, next) => {
  try {
    const { id } = req.params;

    // 检查公告是否存在
    const [existing] = await pool.query('SELECT id FROM announcements WHERE id = ?', [id]);
    if (existing.length === 0) {
      return error(res, '公告不存在', 404);
    }

    await pool.query('UPDATE announcements SET status = "archived" WHERE id = ?', [id]);

    success(res, { id }, '公告已归档');
  } catch (err) {
    next(err);
  }
};

// 获取公告统计（管理员）
exports.getAnnouncementStats = async (req, res, next) => {
  try {
    const [stats] = await pool.query(`
      SELECT
        COUNT(*) as total_count,
        SUM(CASE WHEN status = 'draft' THEN 1 ELSE 0 END) as draft_count,
        SUM(CASE WHEN status = 'published' THEN 1 ELSE 0 END) as published_count,
        SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) as archived_count,
        SUM(view_count) as total_views
      FROM announcements
    `);

    success(res, stats[0]);
  } catch (err) {
    next(err);
  }
};

